﻿CREATE TRIGGER [dbo].[TR_Folder_Modify]
	ON [dbo].[Folder]
	AFTER INSERT, UPDATE
	AS
	BEGIN
		SET NOCOUNT ON
		-- When a folder is modified
		DECLARE @updateFolders TABLE ([Id] INT, [Root] BIT, [IsPrivate] BIT, [ExcludeContents] BIT);
		-- Insert the updated folders to the pending list
		INSERT INTO @updateFolders ([Id], [Root], [IsPrivate], [ExcludeContents])
		SELECT [Id], 1, [IsPrivate], 0 FROM INSERTED;

		DECLARE @proceed INT = 1;
		-- While updates are still being made
		WHILE @proceed > 0
		BEGIN
			-- Add all of the subfolders to the list that need to be updated, stop when the 
			-- subfolder itself is set to private to not cascade past that point
			INSERT INTO @updateFolders ([Id], [Root], [IsPrivate], [ExcludeContents])
			SELECT F.[Id], 0, UF.[IsPrivate], F.[IsPrivate]
			FROM [dbo].[Folder] F
			INNER JOIN @updateFolders UF ON F.[ParentFolderId] = UF.[Id]
			WHERE F.[IsParentFolderPrivate] != [UF].[IsPrivate]
			AND UF.[ExcludeContents] = 0
			-- Update the folders that need to be updated, excluding the root folder
			MERGE [dbo].[Folder] AS T
			USING @updateFolders AS S
			ON T.[Id] = S.[Id]
			WHEN MATCHED AND T.[IsParentFolderPrivate] <> S.[IsPrivate] AND S.[Root] = 0
			THEN UPDATE SET [IsParentFolderPrivate] = S.[IsPrivate];

			SET @proceed = @@ROWCOUNT;
		END

		-- For all of the folders that were not excluding their contents,
		-- update their containing images to set the folder privacy on the image
		MERGE [dbo].[Image] AS T
		USING
		(
			SELECT I.[Id], F.[IsPrivate]
			FROM [dbo].[Image] I
			INNER JOIN @updateFolders F ON I.[FolderId] = F.[Id]
			WHERE F.[ExcludeContents] = 0
		) AS S
		ON T.[Id] = S.[Id] WHEN MATCHED THEN UPDATE SET T.[IsFolderPrivate] = S.[IsPrivate];

	END
